﻿using System;
using System.Collections.Generic;
using System.Text;
using Model = SQLServerHealthCheck.Model;
using SQLServerHealthCheck.Model;
using System.Linq;
using System.Data.SqlClient;
using SMO = Microsoft.SqlServer.Management.Smo;
using WMI = Microsoft.SqlServer.Management.Smo.Wmi;
using System.Windows.Forms;
using System.Collections;
using System.IO;

namespace SQLServerHealthCheck.SQLServer
{
    public static class CollectSQLAgentData
    {
        public static List<Exception> errors;
        public static ArrayList arrSQLAgentCheck = new ArrayList();

        public static List<Exception> Collect(List<String> SQLServerInstances)
        {
            try
            {

                errors = new List<Exception>();

                HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"].ToString());

                CollectSQLAgentData.FillSQLAgentData(SQLServerInstances, dc);
            }
            catch (System.Security.SecurityException ex1)
            {
                MessageBox.Show("You don't have permission to delete  file in path " + Application.StartupPath, "HealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            return errors;

        }


        private static void FillSQLAgentData(List<String> SQLServerInstances, HealthCheckDataContext dc)
        {

            foreach (String sqlServInstance in SQLServerInstances)
            {
                FillSQLJobDetails(dc, sqlServInstance);



            }

        }


        private static void FillSQLJobDetails(HealthCheckDataContext dc, String sqlServerInstance)
        {

            try
            {
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_SQLAgentCheckLog.txt", "FillSQLJobDetails: ", " FillSQLJobDetails method in SQLAgentCheck Started for " + service.InstanceName.ToString());

                //Check the connection to SQL Server Instance and gets the details.
                if (CollectServerAndServiceData.IsSQLServerAccessible(@"Server=" + sqlServerInstance + ";database=master;Integrated Security=True"))
                {

                    String[] parts = sqlServerInstance.ToLower().Split('\\');
                    String serverName = parts[0];


                    Server server = dc.Servers.FirstOrDefault(s => s.Name == serverName);

                    if (server != null)
                    {

                        Service service = server.Services.FirstOrDefault(s => s.InstanceName == sqlServerInstance);
                        if (service != null)
                        {




                            using (SqlConnection cn = new SqlConnection(@"Server=" + sqlServerInstance + ";Database=master;Integrated Security=True"))
                            {
                                cn.Open();

                                string sqlOLE1 = "sp_configure 'Agent XPs',1 ;reconfigure;";
                                SqlCommand OLEcmd1 = new SqlCommand(sqlOLE1, cn);
                                OLEcmd1.ExecuteNonQuery();


                                String sql = @"	

CREATE TABLE #Temp1
(
job_id uniqueidentifier NOT NULL,
last_run_date nvarchar (20) NOT NULL,
last_run_time nvarchar (20) NOT NULL,
next_run_date nvarchar (20) NOT NULL,
next_run_time nvarchar (20) NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname
COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
DECLARE @job_owner   sysname
DECLARE @is_sysadmin   INT
SET @is_sysadmin   = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner   = suser_sname ()
INSERT INTO #Temp1
 
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
UPDATE #Temp1
SET last_run_time    = right ('000000' + last_run_time, 6),
next_run_time    = right ('000000' + next_run_time, 6);







SELECT j.name AS JobName,
case j.enabled
when 1 then 'Enabled'
else 'Disabled'
end
 AS Enabled,
CASE h.run_status
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
END
AS LastRunStatus,
c.name as Category,
CAST(
convert(datetime,cast(h.run_date as varchar))
+(cast(h.run_time as float)*(.36)+cast(h.run_time%10000 as float)*(.6-.36)+cast(h.run_time%100 as float)*(1-.6)+ run_duration)/86400

as varchar) as LastRunDate,

CASE
WHEN h.run_duration > 0
THEN
(h.run_duration / 1000000) * (3600 * 24)
+ (h.run_duration / 10000 % 100) * 3600
+ (h.run_duration / 100 % 100) * 60
+ (h.run_duration % 100)
ELSE
NULL
END
AS [Duration(ms)]


FROM         #Temp1 x
LEFT JOIN
msdb.dbo.sysjobs j
ON x.job_id = j.job_id
LEFT OUTER JOIN
msdb.dbo.syscategories c
ON j.category_id = c.category_id
LEFT OUTER JOIN
msdb.dbo.sysjobhistory h
ON     x.job_id = h.job_id
AND x.last_run_date = h.run_date
AND x.last_run_time = h.run_time
AND h.step_id = 0

drop table #Temp1
";



                                SqlCommand cmd = new SqlCommand(sql, cn);
                                SqlDataReader rd = cmd.ExecuteReader();


                                while (rd.Read())
                                {
                                    SQLAgentJobDetails ajd = null;


                                    if (service.SQLAgentJobDetails.Count(d => d.JobName == rd[1].ToString()) == 0)
                                    {
                                        ajd = new SQLAgentJobDetails();

                                        ajd.JobName = rd[0].ToString();
                                        ajd.Enabled = rd[1].ToString();
                                        ajd.LastRunStatus = rd[2].ToString();
                                        ajd.Category = rd[3].ToString();
                                        ajd.LastRunDateTime = rd[4].ToString();
                                        ajd.Duration = rd.IsDBNull(5) ? (int?)null : Convert.ToInt32(rd[5].ToString());

                                        service.SQLAgentJobDetails.Add(ajd);
                                    }
                                    else
                                    {
                                        ajd = service.SQLAgentJobDetails.First(d => d.JobName == rd[1].ToString());
                                    }
                                }


                                rd.Close();


                            }


                            dc.SubmitChanges();
                            // InstallationCheck.WriteLoginFile("SQLServerHealthCheck_SQLAgentCheckLog.txt", "FillSQLJobDetails: ", " FillSQLJobDetails method in SQLAgentCheck Completed for " + service.InstanceName.ToString());

                        }
                    }
                }

                else
                {
                    //Add the name of SQL Intances for which connections were not sucessfull and details were not imported

                    arrSQLAgentCheck.Add(sqlServerInstance);
                    //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_SQLAgentCheckLog.txt", "FillTables: ", " There were problems in importing PerformanceCheck details for " + service.InstanceName.ToString() + ".Please check the connectivity to this instance");
                }




            }


            catch (Exception ex)
            {


                errors.Add(ex);
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_SQLAgentCheckLog.txt", "FillSQLJobDetails: ", " Some error occured in SQLAgentCheck.Please check exception file for details");


            }



        }






    }
}
